﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using System.Data.SqlClient;

namespace WorkerRole.Main
{
    class BackupSqlScripts
    {

        public static void GenerateSqlScripts(SqlConnection cn, DatabaseInfo databaseInfo, string fileName) {
            
            StringBuilder sb = new StringBuilder();

            String dbName = databaseInfo.DatabaseName;

            // Connect to the local, default instance of SQL Server. 
            ServerConnection con=new ServerConnection();
            con.ServerInstance = databaseInfo.ServerName;
            con.LoginSecure = false;
            con.Login = databaseInfo.LoginId;
            con.Password = databaseInfo.Password;
            con.DatabaseName = databaseInfo.DatabaseName;

            Server srv = new Server(con);
            
            // Reference the database.  
            Database db = srv.Databases[dbName];

            // Define a Scripter object and set the required scripting options. 
            Scripter scrp = new Scripter(srv);
            scrp.Options.ScriptDrops = false;
            scrp.Options.WithDependencies = true;
            scrp.Options.Indexes = true;   // To include indexes
            scrp.Options.DriAllConstraints = true;   // to include referential constraints in the script

            // Iterate through the tables in database and script each one. Display the script.   
            foreach (Table tb in db.Tables)
            {
                // check if the table is not a system table
                if (tb.IsSystemObject == false)
                {
                    // Generating script for table tb
                    System.Collections.Specialized.StringCollection sc = scrp.Script(new Urn[] { tb.Urn });
                    foreach (string st in sc)
                    {
                        sb.AppendLine(st);
                    }
                }
            }

            System.IO.StreamWriter file = new System.IO.StreamWriter("c:\\bcp\\" + fileName, false);
            file.WriteLine(sb.ToString());
            file.Close();

        }
    }
}
